package repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.HuishiUtil;

import model.TradeDO;

import com.iwallet.biz.common.util.money.Money;

import dao.MyDataSource;

public class JDBCTradeRepositoryImpl implements TradeRepository {
	private  Connection conn;
	private MyDataSource ds;
	private Statement st = null;
	private PreparedStatement pst = null;

	@Override
	public int createTrade(String sellerAccNo, String buyerAccNo,
			Money amount, String commodity, String tradeType) {
		try{
			HuishiUtil.sqlInjectionCheck(sellerAccNo,buyerAccNo,commodity,tradeType);
			conn = ds.buildUpConnection();
			
			String sql = "insert into trade_40441 (seller_acc_no,buyer_acc_no,commodity,amount,status,trade_type,gmt_create,gmt_modified) "+
					"values (?,?,?,?,'S',?,now(),now()"+")";
			
			pst = conn.prepareStatement(sql,new String[]{"trade_no"});
			pst.setString(1, sellerAccNo);
			pst.setString(2, buyerAccNo);
			pst.setString(3, commodity);
			pst.setLong(4, amount.getCent());
			pst.setString(5, tradeType);
			
		pst.executeUpdate();
		ResultSet rs = pst.getGeneratedKeys();
		if(rs == null || !rs.next()) return 0;
		int generatedKey = rs.getInt(1);
		return generatedKey;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}	finally{
			HuishiUtil.closeStatement(pst);
			ds.tearDownConnection();
		}
	}

	@Override
	public List<TradeDO> queryAccountTrade(String accountNo, Date startDate,
			Date endDate, String status, String tradeType) {
		try{
			conn = ds.buildUpConnection();
			st = conn.createStatement();
			String sql = "select trade_no,seller_acc_no,buyer_acc_no,commodity,amount,status,trade_type,gmt_create,gmt_modified from trade_40441 where"+
					"(seller_acc_no = '"+accountNo+"' or buyer_acc_no = '" + accountNo + "') " +
					(startDate == null?"":"and gmt_create >= '" + HuishiUtil.formatDate(startDate) + "' ") +
					(endDate == null?"":"and gmt_create <= '" + HuishiUtil.formatDate(endDate) + "' ") +
					(HuishiUtil.isBlank(status)?"":"and status = '" + status + "' ") +
					(HuishiUtil.isBlank(tradeType)?"":"and trade_type = '" + tradeType + "' ") +
					"order by gmt_create desc";
		ResultSet rs = st.executeQuery(sql);
		
		return convertRS2DOList(rs);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	finally{
			HuishiUtil.closeStatement(st);
			ds.tearDownConnection();
		}
	}
	
	private List<TradeDO> convertRS2DOList(ResultSet rs) {
		List<TradeDO> tradeDOList = new ArrayList<TradeDO>();
		try {
			while(rs.next()){
				TradeDO tmpTrade = new TradeDO();
				int amount = rs.getInt("amount");
				tmpTrade.setAmount(new Money(amount/100,amount));
				tmpTrade.setBuyerAccNo(rs.getString("buyer_acc_no"));
				tmpTrade.setCommodity(rs.getString("commodity"));
				tmpTrade.setGmtCreate(rs.getTimestamp("gmt_create"));
				tmpTrade.setGmtModified(rs.getTimestamp("gmt_modified"));
				tmpTrade.setSellerAccNo(rs.getString("seller_acc_no"));
				tmpTrade.setStatus(rs.getString("status"));
				tmpTrade.setTradeNo(rs.getInt("trade_no"));
				tmpTrade.setType(rs.getString("trade_type"));
				
				tradeDOList.add(tmpTrade);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tradeDOList;
	}

	public MyDataSource getDs() {
		return ds;
	}


	public void setDs(MyDataSource ds) {
		this.ds = ds;
	}


}
